package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.EcProductLogoRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.ectemplate.EcProductLogo;
import com.ndp.fb.rdb.model.ectemplate.EcTemplateElementShowResult;
import com.ndp.fb.rdb.model.ectemplate.EcTemplateGroupCnt;
import com.ndp.fb.util.SQLUtil;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by sisi.chen on 2016/7/6.
 */
@Service
public class EcProductLogoRdbServiceImpl extends DaoTemplate implements EcProductLogoRdbService {
    @Override
    public List<Long> batchSave(List<EcProductLogo> list) {
        return batchSaveResultId(list);
    }

    @Override
    public List<EcProductLogo> filterByTemplate(Long templateId, String color, Long productId, Integer major) {
        String sql = "select * from ec_product_logo logo\n" +
                "left Join ec_color_type color on  color.id = logo.element_color_type_id\n" +
                "where logo.element_style_group_id in (\n" +
                "select distinct element_style_group_id from ec_template_element_style where template_id = ? )\n" +
                "and logo.product_id = ? and logo.major = ?\n" +
                "and color.`name` = ?  order by logo.element_style_group_id asc,logo.create_time desc";
        return getJdbcTemplate().query(sql, new Object[]{templateId, productId, major, color}, DefaultRowMapper.autoRowMapper(EcProductLogo.class));
    }

    @Override
    public List<EcProductLogo> filter(String color, Long productId, int major) {
        String sql = "select * from ec_product_logo logo\n" +
                "inner join ec_color_type color on  color.id = logo.element_color_type_id\n" +
                "where\n" +
                "logo.product_id = ? and logo.major = ? \n" +
                "and color.`name` = ? order by logo.element_style_group_id asc,logo.create_time desc";
        return getJdbcTemplate().query(sql, new Object[]{productId, major, color}, DefaultRowMapper.autoRowMapper(EcProductLogo.class));
    }

    @Override
    public List<EcProductLogo> findByProductIdAndElementName(Long productId, String elementName) {
        String sql = "select * from ec_product_logo where product_id = ? and element_name = ?";
        return getJdbcTemplate().query(sql, new Object[]{productId, elementName}, DefaultRowMapper.autoRowMapper(EcProductLogo.class));
    }

    @Override
    public Long countProductByTemplateId(Long templateId) {
        String sql = "select COUNT(DISTINCT pg.product_id) as cnt from ec_product_element_style_group pg,ec_template_element_style tg\n" +
                "where tg.template_id = ? and pg.element_style_group_id = tg.element_style_group_id";

        Object count = getJdbcTemplate().queryForMap(sql, new Object[]{templateId}).get("cnt");
        return Long.valueOf(count.toString());
    }

    @Override
    public List<EcTemplateGroupCnt> getProductByTemplateId(List<Long> templateIds) {
        String sql = "SELECT tg.template_id, COUNT(DISTINCT pg.product_id) AS cnt FROM ec_product_element_style_group pg,ec_template_element_style tg\n" +
                "WHERE pg.element_style_group_id = tg.element_style_group_id AND tg.template_id IN (" + SQLUtil.appendPlaceholder(templateIds.size()) + ") GROUP BY tg.template_id";

        return getJdbcTemplate().query(sql, templateIds.toArray(), DefaultRowMapper.autoRowMapper(EcTemplateGroupCnt.class));
    }

    @Override
    public Long countProductByOrgId(Long orgId) {
        String sql = "select COUNT(DISTINCT pg.product_id) as cnt from ec_product_element_style_group pg,product_manage p\n" +
                "where pg.product_id = p.product_id and p.org_id = ?";

        Object count = getJdbcTemplate().queryForMap(sql, new Object[]{orgId}).get("cnt");
        return Long.valueOf(count.toString());
    }

    @Override
    public List<EcProductLogo> findByStyleGroupsAndelementColorTypeIdPager(Long orgId, String styleGroupIds, String elementColorType,
                                                                           Integer firstResult, Integer sizeNo) {
        List<Object> params = new ArrayList<>();
        String sql = "select n.* ,logo.*, color.name as color_name from (" +
                "select distinct p.product_id,p.product_name,psg.time as relative_time from ec_product_element_style_group psg left join product_manage p " +
                "on  psg.product_id = p.product_id " +
                "where psg.element_style_group_id in (" + styleGroupIds + ")  and p.org_id = ? group by p.product_id order by  relative_time desc limit ?,? ) n " +
                "left Join ec_product_logo logo on  n.product_id = logo.product_id  " +
                "left Join ec_color_type color on  color.id = logo.element_color_type_id  " +
                "where logo.element_style_group_id in (" + styleGroupIds + ")  ";
        params.add(orgId);
        params.add(firstResult);
        params.add(sizeNo);
        if (!StringUtils.isEmpty(elementColorType)){
            //按颜色查询
            sql += " and color.name =  ? ";
            params.add(elementColorType);
        }
        return getJdbcTemplate().query(sql, params.toArray(), DefaultRowMapper.autoRowMapper(EcProductLogo.class));
    }

    @Override
    public List<EcProductLogo> findByElementColorTypeIdPager(String elementColorType, Long orgId, Integer firstResult, Integer sizeNo) {
        List<Object> params = new ArrayList<>();
        String sql = "select n.* ,logo.*, color.name as color_name from (select DISTINCT p.product_id, p.product_name,psg.time as relative_time from ec_product_element_style_group psg ,product_manage p\n" +
                "where psg.product_id = p.product_id and p.org_id = ? \n" +
                "group by p.product_id order by relative_time desc\n" +
                "limit ?,?) n\n" +
                "inner join ec_product_logo logo on  n.product_id = logo.product_id\n" +
                "inner Join ec_color_type color on  color.id = logo.element_color_type_id\n";
        params.add(orgId);
        params.add(firstResult);
        params.add(sizeNo);
        if (!StringUtils.isEmpty(elementColorType)){
            //按颜色查询
            sql += " where color.name = ? ";
            params.add(elementColorType);
        }
        return getJdbcTemplate().query(sql, params.toArray(), DefaultRowMapper.autoRowMapper(EcProductLogo.class));
    }

    @Override
    public List<EcTemplateElementShowResult> findByFeedIdAndTemplateIdAndMajorAndOrgId(Long feedId, Long templateId, Boolean major, Long orgId) {
        String sql = "select distinct logo.*,c.name as element_color_name, es.code as style_code  from ec_template_element_style ts, ec_product_logo logo,ec_element_style es, ec_color_type c,\n" +
                "product_we_feed feed\n" +
                "where ts.element_style_group_id = logo.element_style_group_id and logo.element_style_id = es.id and c.id = logo.element_color_type_id \n" +
                "and feed.product_id = logo.product_id\n" +
                "and ts.template_id = ?\n" +
                "and logo.org_id = ?\n" +
                "and logo.major = ?\n" +
                "and feed.id = ?;";
        return getJdbcTemplate().query(sql, new Object[]{templateId, orgId, major, feedId}, DefaultRowMapper.autoRowMapper(EcTemplateElementShowResult.class));
    }

    @Override
    public EcTemplateElementShowResult findByIdAndTemplateId(Long id, Long ecTemplateId) {
        String sql = "select logo.*,c.name as element_color_name, es.code as style_code \n" +
                "from ec_product_logo logo,ec_element_style es, ec_template_element_style tes, ec_color_type c\n" +
                "where es.id = logo.element_style_id  and tes.element_style_group_id = es.style_group  and logo.element_color_type_id = c.id\n" +
                "and logo.id = ? and tes.template_id = ?;";
        return findOne(sql, new Object[]{id, ecTemplateId}, DefaultRowMapper.autoRowMapper(EcTemplateElementShowResult.class));
    }
}
